from ..database import connect_to_database, close_database_connection

def add_recipe(user_id: str, dish_name: str, ingredients: str, instructions: str):
    """
    添加食谱
    :param user_id: 用户 ID
    :param dish_name: 菜品名称
    :param ingredients: 食材（Markdown 格式）
    :param instructions: 做法（Markdown 格式）
    :return: 添加结果
    """
    db, ssh_tunnel = connect_to_database()
    query = """
    INSERT INTO user_recipes (user_id, dish_name, ingredients, instructions)
    VALUES (%s, %s, %s, %s)
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query, (user_id, dish_name, ingredients, instructions))
            db.commit()  # 提交事务
            return {"message": "食谱添加成功", "recipe_id": cursor.lastrowid}
    except Exception as e:
        db.rollback()  # 回滚事务
        raise Exception(f"Failed to add recipe: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)

def get_user_recipes(user_id: str):
    """
    获取用户的食谱列表
    :param user_id: 用户 ID
    :return: 用户的食谱列表
    """
    db, ssh_tunnel = connect_to_database()
    query = """
    SELECT recipe_id, dish_name, ingredients, instructions, created_at
    FROM user_recipes
    WHERE user_id = %s
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query, (user_id,))
            results = cursor.fetchall()

            if not results:
                return None

            recipes = []
            for row in results:
                recipes.append({
                    "recipe_id": row[0],
                    "dish_name": row[1],
                    "ingredients": row[2],
                    "instructions": row[3],
                    "created_at": row[4]
                })

            return recipes
    except Exception as e:
        raise Exception(f"Database query error: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)

def delete_recipe(recipe_id: int):
    """
    删除食谱
    :param recipe_id: 食谱 ID
    :return: 删除结果
    """
    db, ssh_tunnel = connect_to_database()
    query = """
    DELETE FROM user_recipes
    WHERE recipe_id = %s
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query, (recipe_id,))
            db.commit()  # 提交事务
            return {"message": "食谱删除成功"}
    except Exception as e:
        db.rollback()  # 回滚事务
        raise Exception(f"Failed to delete recipe: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)

def update_recipe(recipe_id: int, dish_name: str, ingredients: str, instructions: str):
    """
    修改食谱
    :param recipe_id: 食谱 ID
    :param dish_name: 菜品名称
    :param ingredients: 食材（Markdown 格式）
    :param instructions: 做法（Markdown 格式）
    :return: 修改结果
    """
    db, ssh_tunnel = connect_to_database()
    query = """
    UPDATE user_recipes
    SET dish_name = %s, ingredients = %s, instructions = %s
    WHERE recipe_id = %s
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query, (dish_name, ingredients, instructions, recipe_id))
            db.commit()  # 提交事务
            return {"message": "食谱修改成功"}
    except Exception as e:
        db.rollback()  # 回滚事务
        raise Exception(f"Failed to update recipe: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)